﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using MSL.DataBase.Lib;
using System.Collections;
using System.Diagnostics;

namespace MSL.DataBase.Core
{
    internal class SQLHelper : DbBase//
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conn">链接字符串</param>
        public SQLHelper(string conn)
            : base(DataBaseType.Sql, conn, DBConfig.SqlClient)
        {

        }
        public SQLHelper()
            : base(DataBaseType.Sql, DBConfig.ConnString, DBConfig.SqlClient)
        {

        }
        public override void AddReturnPara()
        {
            AddParameters("@TotalCount", null, DbType.Int32, -1, ParameterDirection.Output);
        }
        internal override void AddCustomePara(string paraName, ParaType paraType)
        {
            switch (paraType)
            {
                case ParaType.OutPut:
                case ParaType.result:
                    SqlParameter para = new SqlParameter();
                    para.ParameterName = paraName;
                    para.SqlDbType = SqlDbType.Int;
                    if (paraType == ParaType.OutPut)
                    {
                        para.Direction = ParameterDirection.Output;
                    }
                    else
                    {
                        para.Direction = ParameterDirection.ReturnValue;
                    }
                    Com.Parameters.Add(para);
                    break;
            }
        }
        public void AddParameters(string parameterName, object value, DbType dataType, int size)
        {
            AddParameters(parameterName, value, dataType, size, ParameterDirection.Input);
        }
        public override System.Data.Common.DbParameter GetNewParameter()
        {
            return new SqlParameter();
        }

        #region 分页

        public override DataTable Query(string tableName, string primaryKey, string showFields, int pageIndex, int pageSize, string where, string sortBy, string groupBy, out int rowCount)
        {
            rowCount = 0;
            DataTable dt = null;
            Hashtable parameters = new Hashtable();

            parameters.Add("@Tables", tableName);
            parameters.Add("@PageIndex", pageIndex);
            parameters.Add("@PageSize", pageSize);
            parameters.Add("@Fields", showFields);
            parameters.Add("@PrimaryKey", primaryKey);//排序使用
            parameters.Add("@Group", null);//分组
            parameters.Add("@Sort", sortBy);
            parameters.Add("@Filter", where);

            dt = Query("sp_Paging", true, parameters);
            rowCount = base.recoedTotal;
            return dt;
        }
        #endregion

        #region 批量操作

        /// <summary>
        /// 大批量数据插入
        /// </summary>
        /// <param name="table">数据表</param>
        public override void BulkInsert(DataTable table)
        {
            if (string.IsNullOrEmpty(table.TableName)) throw new Exception("DataTable.TableName属性不能为空");

            using (SqlBulkCopy bulk = new SqlBulkCopy(base._com.Connection.ConnectionString))
            {
                bulk.BatchSize = base.batchSize;
                bulk.BulkCopyTimeout = base.commandTimeOut;
                bulk.DestinationTableName = table.TableName;
                foreach (DataColumn col in table.Columns)
                {
                    bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                }
                bulk.WriteToServer(table);
                bulk.Close();
            }
        }

        #endregion 批量操作

        #region 数据库管理

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="backfile">备份文件</param>
        /// <returns>还原成功与否提示</returns>
        public override bool RestoreDatabase(string dbName, string backfile)
        {
            ///杀死原来所有的数据库连接进程
            bool flag = false;

            string sql = "SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='" + dbName + "'";

            ArrayList list = new ArrayList();
            try
            {
                base.Open();
                base._com.Connection.ChangeDatabase("master");
                DbDataReader dr = base.ExecReader(sql, false);
                while (dr.Read())
                {
                    list.Add(dr.GetInt16(0));
                }
                dr.Close();
            }
            catch (SqlException ex)
            {
                flag = false;
                Debug.Print(ex.ToString());
                throw ex;
            }
            finally
            {
                base.Close();
            }
            Debug.Print("使用的线程数" + list.Count.ToString());
            for (int i = 0; i < list.Count; i++)
            {
                sql = String.Format("KILL {0}", list[i].ToString());
                base.ExecSql(sql, false);
                Debug.Print("you have killed thread " + list[i].ToString());
            }

            string killThread = String.Format("declare  hcforeach   cursor   global   for " +
    "select   'kill   '+rtrim(spid)   from   master.dbo.sysprocesses   where   dbid=db_id(N'{0}')   exec   sp_msforeach_worker   '?'", dbName);
            //还原前，kill进程
            base.ExecSql(killThread, false);

            //这里一定要是master数据库，而不能是要还原的数据库，因为这样便变成了有其它进程
            //占用了数据库。
            string BACKUP = String.Format("RESTORE DATABASE {0}  FROM DISK = '{1}'", dbName, backfile);

            try
            {
                flag = true;
                base.ExecSql(BACKUP, false);
                //Debug.Print("还原成功");
            }
            catch (SqlException ee)
            {
                flag = false;
                throw (ee);
                //Debug.Print("还原失败");
                //Debug.Print(ee.ToString());
            }
            finally
            {

            }
            return flag;
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="dbFileName">备份文件</param>
        /// <returns>是否备份成功</returns>
        public override bool BackDataBase(string dbName, string dbFileName)
        {
            bool flag = false;

            //备份前，Kill掉进程
            string killThread = String.Format("declare  hcforeach   cursor   global   for " +
    "select   'kill   '+rtrim(spid)   from   master.dbo.sysprocesses   where   dbid=db_id(N'{0}')   exec   sp_msforeach_worker   '?'", dbName);
            //还原前，kill进程
            base.ExecSql(killThread, false);
            string strsql = String.Format("backup database {0} to disk = '{1}'", dbName, dbFileName);
            try
            {
                int index = base.ExecSql(strsql, false);
                if (index != 0) flag = true;
            }
            catch (Exception)
            {
                flag = false;
                throw new Exception("数据库备份时出错了！");
            }
            return flag;
        }

        /// <summary>
        /// 新建数据库
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        public override bool CreateDataBase(string databaseName)
        {
            bool flag = false;
            //调用ExecuteNonQuery()来创建数据库 
            string sql = String.Format("CREATE DATABASE {0}", databaseName);

            try
            {
                base.Open();
                base._com.Connection.ChangeDatabase("master");
                base.ExecSql(sql, false);
                flag = true;
            }
            catch (Exception e)
            {
                Lib.Log.WriteLog(e.Message);
            }
            finally
            {
                base.Close();
            }
            return flag;
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        public override bool DropDataBase(string databaseName)
        {
            bool flag = false;
            //删除前，Kill掉进程
            string killThread = String.Format("declare  hcforeach   cursor   global   for " +
            "select   'kill   '+rtrim(spid)   from   master.dbo.sysprocesses   where   dbid=db_id(N'{0}')   exec   sp_msforeach_worker   '?'", databaseName);
            DbCommand com = base._com;
            try
            {
                base.Open();
                base._com.Connection.ChangeDatabase("master");
                base.ExecSql(killThread, false);
                //调用ExecuteNonQuery()来删除数据库 
                string dropSql = String.Format("DROP  DATABASE {0}", databaseName);
                base.ExecSql(dropSql, false);
                flag = true;
            }
            catch (Exception e)
            {
                Lib.Log.WriteLog(e.Message);
            }
            finally
            {
                base.Close();
            }
            return flag;
        }
        #endregion
    }
}
